package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;
import java.util.LinkedHashSet;
import java.util.Set;

import objects.ActorObject;
import objects.CategoryObject;
import objects.EpisodeObject;
import objects.SeasonObject;
import objects.SerialContainer;
import objects.SerialObject;
import utils.DaoConstants;

public class DaoSave extends DaoGeneric<SerialObject> {

	private final static String TABLENAME = DbTables.USER_LOGIN.toString();

	protected DaoSave(Connection con) {
		super(con, TABLENAME);
	}

	public int saveSerial(SerialContainer serialContainer) throws SQLException {
		return saveSerialToDB(serialContainer);
	}

	private int saveSerialToDB(SerialContainer serialContainer)
			throws SQLException {
		PreparedStatement preparedStatement = null;
		ResultSet generatedKeys = null;
		int serialID = 0;

		String insertSerialQuery = "INSERT INTO SERIAL"
				+ "(SERIAL_TITLE, SERIAL_ORIGTITLE, YEAR_START, YEAR_END, SERIAL2CATEGORY, PICPATH) VALUES"
				+ "(?,?,?,?,?,?)";

		try {
			for (SerialObject elemSerial : serialContainer.getSerialList()) {
				preparedStatement = con.prepareStatement(insertSerialQuery,
						Statement.RETURN_GENERATED_KEYS);
				preparedStatement.setString(1, elemSerial.getSerialTitle());
				preparedStatement.setString(2, elemSerial.getSerialOrgTitle());
				preparedStatement.setInt(3, elemSerial.getYearStart());
				preparedStatement.setInt(4, elemSerial.getYearEnd());
				preparedStatement.setInt(5, elemSerial.getSerial2category());
				preparedStatement.setString(6, elemSerial.getPicPath());

				preparedStatement.executeUpdate();

				generatedKeys = preparedStatement.getGeneratedKeys();
				if (generatedKeys.next()) {
					serialID = (int) generatedKeys.getLong(1);
				}
				System.out.println("Records are inserted into tables!");
			}

		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}
		return serialID;
	}

	public int[] saveSeasonToDB(SerialContainer serialContainer, int serialID)
			throws SQLException {
		PreparedStatement preparedStatement = null;
		ResultSet generatedKeys = null;
		int i = 0;
		int seasonIdArray[] = null;

		String insertSerialQuery = "INSERT INTO SEASON"
				+ "(SEASON_NUM, SEASON_NAME, SEASON2SERIAL) VALUES" + "(?,?,?)";

		try {
			int count = 1;
			for (SeasonObject elem : serialContainer.getSeasonList()) {
				preparedStatement = con.prepareStatement(insertSerialQuery,
						Statement.RETURN_GENERATED_KEYS);
				preparedStatement.setInt(1, count);
				preparedStatement.setString(2, elem.getSeasonName());
				preparedStatement.setInt(3, serialID);

				preparedStatement.executeUpdate();
				count++;

			}
			seasonIdArray = new int[count];
			generatedKeys = preparedStatement.getGeneratedKeys();
			while (generatedKeys.next()) {
				seasonIdArray[i] = (int) generatedKeys.getLong(1);
				i++;
				System.out.println("Records are inserted into tables!");
			}

		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}
		return seasonIdArray;
	}
	
	public void saveNewSeasonToDB(SeasonObject seasonObject)
			throws SQLException {
		PreparedStatement preparedStatement = null;
		
		String insertSerialQuery = "INSERT INTO SEASON"
				+ "(SEASON_NUM, SEASON_NAME, SEASON2SERIAL) VALUES" + "(?,?,?)";
		
		try {
				preparedStatement = con.prepareStatement(insertSerialQuery);
				preparedStatement.setInt(1, seasonObject.getSeasonNum());
				preparedStatement.setString(2, seasonObject.getSeasonName());
				preparedStatement.setInt(3, seasonObject.getSeason2serial());
				
				preparedStatement.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}
	}

	public Set<SeasonObject> getSeasonList(SerialContainer serialContainer,
			int serialID) throws SQLException {

		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		SeasonObject seasonObject = null;
		Set<SeasonObject> seasonList = new LinkedHashSet<SeasonObject>();

		String insertSerialQuery = "select * from season where season2serial = ?";

		try {
			preparedStatement = con.prepareStatement(insertSerialQuery);
			preparedStatement.setInt(1, serialID);

			rs = preparedStatement.executeQuery();

			while (rs.next()) {
				seasonObject = new SeasonObject(
						rs.getInt(DaoConstants.ID_NUM_SN),
						rs.getInt(DaoConstants.SEASON_NUM),
						rs.getString(DaoConstants.SEASON_NAME),
						rs.getInt(DaoConstants.SEASON_YEAR),
						rs.getInt(DaoConstants.SEASON2SERIAL));
				seasonList.add(seasonObject);
			}

		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}
		return seasonList;
	}

	public int[] saveEpisodesToDB(SerialContainer serialContainer)
			throws SQLException {

		PreparedStatement preparedStatement = null;

		String insertSerialQuery = "INSERT INTO EPISODE"
				+ "(EPISODE_NAME, EPISODE_NUM, EPISODE2SEASON) VALUES"
				+ "(?,?,?)";

		try {
			for (EpisodeObject elem : serialContainer.getEpisodeList()) {
				preparedStatement = con.prepareStatement(insertSerialQuery);
				preparedStatement.setString(1, elem.getEpisodeName());
				preparedStatement.setInt(2, 1);
				preparedStatement.setInt(3, elem.getEpisode2season());

				preparedStatement.executeUpdate();

			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}
		return null;
	}
	
	public int[] saveEpisode(EpisodeObject epObject, int seasonID)
			throws SQLException {
		
		PreparedStatement preparedStatement = null;
		
		String insertSerialQuery = "INSERT INTO EPISODE"
				+ "(EPISODE_NAME, EPISODE_NUM, EPISODE_DESC, EPISODE2SEASON) VALUES"
				+ "(?,?,?,?)";
		
		try {
				preparedStatement = con.prepareStatement(insertSerialQuery);
				preparedStatement.setString(1, epObject.getEpisodeName());
				preparedStatement.setInt(2, epObject.getEpisodeNum());
				preparedStatement.setString(3, epObject.getEpisodeDescr());
				preparedStatement.setInt(4, seasonID);
				
				preparedStatement.executeUpdate();
				
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}
		return null;
	}

	public Set<CategoryObject> getCategoryList() throws SQLException {

		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		CategoryObject catObject = null;
		Set<CategoryObject> categoryList = new LinkedHashSet<CategoryObject>();

		String insertSerialQuery = "select * from category";

		try {
			preparedStatement = con.prepareStatement(insertSerialQuery);

			rs = preparedStatement.executeQuery();

			while (rs.next()) {
				catObject = new CategoryObject(
						rs.getInt(DaoConstants.ID_NUM_CY),
						rs.getString(DaoConstants.CATEGORY_NAME),
						rs.getString(DaoConstants.CATEGORY_DESC));
				categoryList.add(catObject);
			}

		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}
		return categoryList;
	}

	public Timestamp getCurrentTimeStamp() {
		Date today = new java.util.Date();
		return new Timestamp(today.getTime());
	}

	public void saveActor(Set<ActorObject> actorList, int serialID) throws SQLException {
		PreparedStatement preparedStatement = null;
		ResultSet generatedKeys = null;
		int actorId = 0;
		int mtmId = 0;
		
		String insertActorQuery = "INSERT INTO ACTOR"
				+ "(ACTOR_LASTNAME, ACTOR_FIRSTNAME) VALUES"
				+ "(?,?)";
		
		String insertMTMQuery = "INSERT INTO MTM_ACTOR (SERIAL_ID, ACTOR_ID) VALUES (?,?)";
		
		String insertSerialActor = "UPDATE SERIAL SET SERIAL2MTMACTOR = ? WHERE ID_NUM_SL = ?";
		
		try {
			for (ActorObject actorObject : actorList) {
				preparedStatement = con.prepareStatement(insertActorQuery, Statement.RETURN_GENERATED_KEYS);
				preparedStatement.setString(1, actorObject.getActorLastName());
				preparedStatement.setString(2, actorObject.getActorFirstName());
				preparedStatement.executeUpdate();
				
				generatedKeys = preparedStatement.getGeneratedKeys();
				if (generatedKeys.next()) {
					actorId = (int) generatedKeys.getLong(1);
				}
				preparedStatement.close();
				
				preparedStatement = con.prepareStatement(insertMTMQuery, Statement.RETURN_GENERATED_KEYS);
				preparedStatement.setInt(1, serialID);
				preparedStatement.setInt(2, actorId);
				preparedStatement.executeUpdate();
				
				generatedKeys = preparedStatement.getGeneratedKeys();
				if (generatedKeys.next()) {
					mtmId = (int) generatedKeys.getLong(1);
				}
				preparedStatement.close();
				
				preparedStatement = con.prepareStatement(insertSerialActor);
				preparedStatement.setInt(1, mtmId);
				preparedStatement.setInt(2, serialID);
				preparedStatement.executeUpdate();
			}
				
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}
		
	}

}
